﻿using PlutoStudio.Data.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;

namespace PlutoStudio.Data
{
    public abstract class DataAccessorBase
    {
        protected DbConnection GetConnection(int databaseId)
        {
            var connectionString = Common.ConnectionStringPool.GetConnectionString(databaseId);
            var connection = Common.SqlClientProviderFactory.Provider.GetConnection(connectionString);
            return connection;
        }

        protected int GetDatabaseId(object item)
        {
            return item.GetDatabaseId();
        }

        protected string GetTableName(object item)
        {
            return item.GetTableName();
        }

        protected DbParameter GenerateDbParameter(object item, string name)
        {
            var property = item.GetProperty(name);
            return GenerateDbParameter(item, property);
        }

        protected string GenerateDbIdentifier(string name)
        {
            var provider = SqlClientProviderFactory.Provider;
            return provider.GenerateIdentifier(name);
        }

        protected string GenerateDbIdentifier(PropertyItem property)
        {
            return GenerateDbIdentifier(property.ColumnName);
        }


        protected string GenerateDBParameterName(string name)
        {
            var provider = SqlClientProviderFactory.Provider;
            return provider.GenerateParameterName(name);
        }

        protected string GenerateDBParameterName(PropertyItem property)
        {
            return GenerateDBParameterName(property.ColumnName);
        }

        protected DbParameter GenerateDbParameter(object item, PropertyItem property)
        {
            return SqlClientProviderFactory.Provider.GenerateParameter(item, property);
        }

        public virtual object GetByPrimaryKey(PrimaryKey primaryKey, Type entityType)
        {
            var sql = GeneratePrimaryKeySelectSqlString(primaryKey);
            var parameters = primaryKey.GetPrimaryKeyParameters();
            var databaseId = primaryKey.GetEntity().GetDatabaseId();
            using (var data = ExecuteReader(databaseId, sql, parameters))
            {
                if (!data.Read())
                {
                    return null;
                }
                return GetResult(data, entityType);
            }
        }

        public virtual long Insert(object item)
        {
            return Insert(item, false);
        }

        public virtual int Update(object item)
        {
            return Update(item, false);
        }

        public virtual long Count(Type type)
        {
            var provider = SqlClientProviderFactory.Provider;
            var sql = string.Format("SELECT COUNT(0) FROM {0}", provider.GenerateIdentifier(type.GetTableName()));
            var databaseId = type.GetDatabaseId();
            return Convert.ToInt64(ExecuteScalar(databaseId, sql));
        }

        public virtual int Delete(object item)
        {
            var keys = PropertyFactory.GetPrimaryKeys(item.GetType());
            var sql = GenerateDeleteSqlString(item, keys);
            var parameters = keys.Select(p => SqlClientProviderFactory.Provider.GenerateParameter(item, p));
            var databaseId = item.GetDatabaseId();
            return ExecuteNonQuery(databaseId, sql, parameters);
        }

        public virtual bool Exists(object item)
        {
            var keys = PropertyFactory.GetPrimaryKeys(item.GetType());
            var sql = GenerateCountSqlString(item, keys);
            var parameters = keys.Select(p => SqlClientProviderFactory.Provider.GenerateParameter(item, p));
            var databaseId = item.GetDatabaseId();
            return Convert.ToInt32(ExecuteScalar(databaseId, sql, parameters)) > 0;
        }

        public long Insert(object item, bool excludeDefaultValue)
        {
            IEnumerable<PropertyItem> properties = PropertyFactory.GetProperties(item.GetType()).Values;
            if (excludeDefaultValue)
            {
                properties = properties.Where(p => !p.Property.IsDefault(item));
            }
            return Insert(item, properties);
        }

        public int Update(object item, bool excludeDefaultValue)
        {
            IEnumerable<PropertyItem> properties = PropertyFactory.GetProperties(item.GetType()).Values;
            if (excludeDefaultValue)
            {
                properties = properties.Where(p => !p.Property.IsDefault(item));
            }
            return Update(item, properties);
        }

        public long Insert(object item, params string[] columns)
        {
            var properties = PropertyFactory.GetProperties(item.GetType()).Values
                .Where(p => columns.Contains(p.PropertyName));
            return Insert(item, properties);
        }

        public int Update(object item, params string[] columns)
        {
            var properties = PropertyFactory.GetProperties(item.GetType()).Values
                .Where(p => columns.Contains(p.PropertyName));
            return Update(item, properties);
        }

        protected virtual long Insert(object item, IEnumerable<PropertyItem> properties)
        {
            var sql = GenerateInsertSqlString(item, properties);
            var parameters = properties.Select(p => SqlClientProviderFactory.Provider.GenerateParameter(item, p));
            var databaseId = item.GetDatabaseId();
            if (properties.Count(p => p.Attribute.IsAutoIncrement) == 0)
                return ExecuteNonQuery(databaseId, sql, parameters);
            var id =Convert.ToInt64( ExecuteScalar(databaseId, sql, parameters));
            var property = properties.First(p => p.Attribute.IsAutoIncrement);
            property.SetValue(item, id);
            return id;
        }

        protected virtual int Update(object item, IEnumerable<PropertyItem> properties)
        {
            var whereCols = PropertyFactory.GetPrimaryKeys(item.GetType());
            var sql = GenerateUpdateSqlString(item, properties);
            var parameters = properties.Select(p => SqlClientProviderFactory.Provider.GenerateParameter(item, p)).Union(
                whereCols.Select(p => SqlClientProviderFactory.Provider.GenerateParameter(item, p)));
            var databaseId = item.GetDatabaseId();
            return ExecuteNonQuery(databaseId, sql, parameters);
        }

        protected virtual string GenerateInsertSqlString(object item, IEnumerable<PropertyItem> properites)
        {
            var provider = SqlClientProviderFactory.Provider;
            var idStr = ";SELECT LAST_INSERT_ID()";
            var sqlTemplate = "INSERT {0} ({1}) VALUES ({2})";
            var tableName = provider.GenerateIdentifier(item.GetTableName());
            var cols = string.Join(",", properites.Select(p => provider.GenerateIdentifier(p.ColumnName)));
            var values = string.Join(",", properites.Select(p => provider.GenerateParameterName(p.ColumnName)));
            var result = string.Format(sqlTemplate, tableName, cols, values);
            if (properites.Count(p => p.Attribute.IsAutoIncrement) > 0)
            {
                result = string.Format("{0}{1}", result, idStr);
            }
            return result;
        }

        protected virtual string GeneratePrimaryKeySelectSqlString(PrimaryKey key)
        {
            var item = key.GetEntity();
            IEnumerable<PropertyItem> properties = PropertyFactory.GetProperties(item.GetType()).Values;
            var provider = SqlClientProviderFactory.Provider;
            var sqlTemplate = "SELECT {0} FROM {1} WHERE {2}";
            var tableName = provider.GenerateIdentifier(item.GetTableName());
            var cols = string.Join(",", properties.Select(p => provider.GenerateIdentifier(p.ColumnName)));
            var where = string.Join(" AND ", key.InternalKeys.Select(p => string.Format("{0} ={1}", provider.GenerateIdentifier(p.ColumnName), provider.GenerateParameterName(p.ColumnName))));
            return string.Format(sqlTemplate, cols, tableName, where);
        }

        protected virtual string GenerateUpdateSqlString(object item, IEnumerable<PropertyItem> properites)
        {
            var whereCols = PropertyFactory.GetPrimaryKeys(item.GetType());
            var provider = SqlClientProviderFactory.Provider;
            var sqlTemplate = "UPDATE {0} SET {1} WHERE {2}";
            var tableName = provider.GenerateIdentifier(item.GetTableName());
            var sets = string.Join(",", properites.Select(p => string.Format("{0} ={1}", provider.GenerateIdentifier(p.ColumnName), provider.GenerateParameterName(p.ColumnName))));
            var where = string.Join(" AND ", whereCols.Select(p => string.Format("{0} ={1}", provider.GenerateIdentifier(p.ColumnName), provider.GenerateParameterName(p.ColumnName))));
            return string.Format(sqlTemplate, tableName, sets, where);
        }

        protected virtual string GenerateDeleteSqlString(object item, IEnumerable<PropertyItem> keys)
        {
            var provider = SqlClientProviderFactory.Provider;
            var sqlTemplate = "DELETE FROM {0} WHERE {1}";
            var tableName = provider.GenerateIdentifier(item.GetTableName());
            var where = string.Join(" AND ", keys.Select(p => string.Format("{0} ={1}", provider.GenerateIdentifier(p.ColumnName), provider.GenerateParameterName(p.ColumnName))));
            return string.Format(sqlTemplate, tableName, where);
        }

        protected virtual string GenerateCountSqlString(object item, IEnumerable<PropertyItem> keys)
        {
            var provider = SqlClientProviderFactory.Provider;
            var sqlTemplate = "SELECT COUNT(0) FROM {0} WHERE {1}";
            var tableName = provider.GenerateIdentifier(item.GetTableName());
            var where = string.Join(" AND ", keys.Select(p => string.Format("{0} ={1}", provider.GenerateIdentifier(p.ColumnName), provider.GenerateParameterName(p.ColumnName))));
            return string.Format(sqlTemplate, tableName, where);
        }
        protected virtual object GetResult(IDataRecord record, Type type)
        {
            var result = Activator.CreateInstance(type);
            var properties = PropertyFactory.GetProperties(type).Values;
            foreach (var item in properties)
            {
                try
                {
                    item.SetValue(result, ChangeType( record[item.ColumnName],item.PropertyType));
                }
                catch { }
            }
            return result;
        }

        private static object ChangeType(object obj, Type conversionType)
        {
            return ChangeType(obj, conversionType, Thread.CurrentThread.CurrentCulture);
        }


        private static object ChangeType(object obj, Type conversionType, IFormatProvider provider)
        {
            #region Nullable
            Type nullableType = Nullable.GetUnderlyingType(conversionType);
            if (nullableType != null)
            {
                if (obj == null)
                {
                    return null;
                }
                return Convert.ChangeType(obj, nullableType, provider);
            }
            #endregion
            if (typeof(System.Enum).IsAssignableFrom(conversionType))
            {
                return Enum.Parse(conversionType, obj.ToString());
            }
            return Convert.ChangeType(obj, conversionType, provider);
        }


        protected internal IDataReader ExecuteReader(int databaseId, string sql, params DbParameter[] parameters)
        {
            return ExecuteReader(databaseId, sql, (IEnumerable<DbParameter>)parameters);
        }

        protected internal IDataReader ExecuteReader(int databaseId, string sql, IEnumerable<DbParameter> parameters)
        {
            var connection = GetConnection(databaseId);
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.AddRange(parameters.ToArray());
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        protected internal object ExecuteScalar(int databaseId, string sql, params DbParameter[] parameters)
        {
            return ExecuteScalar(databaseId, sql, (IEnumerable<DbParameter>)parameters);
        }

        protected internal object ExecuteScalar(int databaseId, string sql, IEnumerable<DbParameter> parameters)
        {
            using (var connection = GetConnection(databaseId))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(parameters.ToArray());
                    return command.ExecuteScalar();
                }
            }
        }

        protected internal int ExecuteNonQuery(int databaseId, string sql, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(databaseId, sql, (IEnumerable<DbParameter>)parameters);
        }

        protected internal int ExecuteNonQuery(int databaseId, string sql, IEnumerable<DbParameter> parameters)
        {
            using (var connection = GetConnection(databaseId))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(parameters.ToArray());
                    return command.ExecuteNonQuery();
                }
            }
        }

        internal protected abstract Type GetEntityType();

        public virtual PrimaryKey GetPrimaryKey(Type entityType)
        {
            return new PrimaryKey(entityType);
        }

        protected virtual IEnumerable<object> GetResultSet(IDataReader recordSet, Type entityType)
        {
            var result = new List<object>();
            while (recordSet.Read())
            {
                result.Add(GetResult(recordSet, entityType));
            }
            return result;
        }
    }
}
